A star schema is a type of data warehouse schema used for reporting data and data analysis. The star schema consists of one or more fact tables referencing any number of dimension tables. A fact table holds metric values recorded for a specific event. A dimension table typically has fewer records compared to a fact table but holds a very large number of attributes that describe the fact data. A data mart is an access layer of the data warehouse environment that is used to get data out to users and different data marts are utilized for different business scenarios.
A time dimension table is essential in OLAP star schema design as it involves time related data. In OLAP star schema design, time related data accounts for approximately 90% of the data collected in OLAP analysis. As a result, a time dimension table is typically the largest type of dimension table and is very expensive to manage utilizing conventional OLAP storage models.
Issues arise when managing time dimension tables using conventional storage models because data in a time dimension table is static and typically generated when the time dimension table is created whereas data in a fact table is dynamic and incrementally grows over time. Additionally, to provide data comparability, a time dimension table with a specific time unit level is shareable among various data marts that have the same specific time unit level. However, because each data mart typically has its own time unit level, data comparability among data marts is limited thereby increasing the size of the time dimension table. Therefore, there is a strong need for a cost-effective solution that overcomes the above issue by efficiently managing and optimizing time dimension tables. The present invention addresses such a need.